package com.java2007.hotel.dao.impl;


import com.java2007.hotel.dao.FoodTableDao;
import com.java2007.hotel.pojo.FoodTable;
import com.java2007.hotel.utils.JdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;

/**
 * 餐桌dao实现类
 * @author csl
 * @version V1.0
 * @date 2021/01/21
 **/
public class FoodTableDaoImpl  implements FoodTableDao {
    /**
     * 增加一个餐桌
     * @param foodTable
     * @return
     */
    @Override
    public int addFoodTable(FoodTable foodTable) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "INSERT INTO t_dinner_table\n" +
                "            (table_name,\n" +
                "             table_status,\n" +
                "             table_reserve_date,\n" +
                "             table_create_date,\n" +
                "             table_update_date,\n" +
                "             table_creater_id)\n" +
                "VALUES (?,?,?,?,?,?)";

        int update = queryRunner.update(JdbcUtils.getConnection(), sql, foodTable.getName(), foodTable.getStatus()
                , foodTable.getReserveDate(), foodTable.getCreateDate(), foodTable.getUpdateDate(), foodTable.getCreaterId());
        return update;
    }

    /**
     * 查询所有餐桌
     * @return
     */
    @Override
    public List<FoodTable> selectAllFoodTable() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "SELECT\n" +
                "  table_id id ,\n" +
                "  table_name name,\n" +
                "  table_status status,\n" +
                "  table_reserve_date reserveDate,\n" +
                "  table_create_date createDate,\n" +
                "  table_update_date updateDate,\n" +
                "  table_creater_id createrId\n" +
                "FROM t_dinner_table";
        List<FoodTable> query = queryRunner.query(sql, new BeanListHandler<FoodTable>(FoodTable.class));
        return query;
    }

    /**
     * 根据id和状态修改餐桌状态
     * @param status
     * @param id
     * @return
     */
    @Override
    public int updateFoodTableStatus(byte status, int id , Date reserveDate,Date updateDate) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "UPDATE t_dinner_table SET table_status = ?, table_reserve_date = ?,table_update_date=? WHERE table_id =?";
        int update = queryRunner.update(JdbcUtils.getConnection(), sql, status,reserveDate,updateDate, id);
        return update;
    }

    /**
     * 根据id删除餐桌
     * @param id
     * @return
     */
    @Override
    public int deleteFoodTableById(int id) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "DELETE FROM t_dinner_table WHERE table_id =?";
        int update = queryRunner.update(JdbcUtils.getConnection(), sql, id);
        return update;
    }

    /**
     * 根据菜的名字查询餐桌
     * @param name
     * @return
     */
    @Override
    public List<FoodTable> selectFoodTableByName(String name) throws SQLException {
        name = "%"+name.trim()+"%";
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "SELECT\n" +
                "  table_id id,\n" +
                "  table_name name,\n" +
                "  table_status status,\n" +
                "  table_reserve_date reserveDate,\n" +
                "  table_create_date createDate,\n" +
                "  table_update_date updateDate,\n" +
                "  table_creater_id createrId\n" +
                "FROM t_dinner_table\n" +
                "WHERE table_name like ?";
        List<FoodTable> query = queryRunner.query(sql, new BeanListHandler<FoodTable>(FoodTable.class), name);
        return query;
    }
    /**
     * 根据餐桌的状态查询餐桌
     * @param status
     * @return
     */
    @Override
    public List<FoodTable> selectFoodTableByStatus(Byte status) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "SELECT\n" +
                "  table_id id,\n" +
                "  table_name name,\n" +
                "  table_status status,\n" +
                "  table_reserve_date reserveDate,\n" +
                "  table_create_date createDate,\n" +
                "  table_update_date updateDate,\n" +
                "  table_creater_id createrId\n" +
                "FROM t_dinner_table\n" +
                "WHERE table_status = ?";
        List<FoodTable> query = queryRunner.query(sql, new BeanListHandler<FoodTable>(FoodTable.class), status);
        return query;
    }

    /**
     * 根据id查询餐桌
     * @param id
     * @return
     */
    @Override
    public FoodTable selectFoodTableById(int id) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "SELECT\n" +
                "  table_id id,\n" +
                "  table_name name,\n" +
                "  table_status status,\n" +
                "  table_reserve_date reserveDate,\n" +
                "  table_create_date createDate,\n" +
                "  table_update_date updateDate,\n" +
                "  table_creater_id createrId\n" +
                "FROM t_dinner_table\n" +
                "WHERE table_id = ?";
        FoodTable query = queryRunner.query(sql, new BeanHandler<>(FoodTable.class), id);
        return query;
    }
    /**
     * 根据餐桌的名字查询餐桌
     * @param name
     * @return
     */
    @Override
    public List<FoodTable> selectFoodTableByNameAccurate(String name) throws SQLException {

        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "SELECT\n" +
                "  table_id id,\n" +
                "  table_name name,\n" +
                "  table_status status,\n" +
                "  table_reserve_date reserveDate,\n" +
                "  table_create_date createDate,\n" +
                "  table_update_date updateDate,\n" +
                "  table_creater_id createrId\n" +
                "FROM t_dinner_table\n" +
                "WHERE table_name = ?";
        List<FoodTable> query = queryRunner.query(sql, new BeanListHandler<FoodTable>(FoodTable.class), name);
        return query;
    }
}
